package com.jeecg.modules.jmreport.test;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
    private static HikariDataSource dataSource;

    static {
        try {
            // 可以从配置文件加载配置
            Properties props = new Properties();
            props.setProperty("jdbcUrl", "jdbc:mysql://127.0.0.1:3306/jimureport?useSSL=false&serverTimezone=UTC&characterEncoding=utf8");
            props.setProperty("username", "weir");
            props.setProperty("password", "weir336393QQ!");
            props.setProperty("driverClassName", "com.mysql.cj.jdbc.Driver");
            props.setProperty("poolName", "HikariPool-1");
            props.setProperty("maximumPoolSize", "10");
            props.setProperty("minimumIdle", "5");
            props.setProperty("idleTimeout", "30000");
            props.setProperty("maxLifetime", "1800000");
            props.setProperty("connectionTimeout", "30000");

            HikariConfig config = new HikariConfig(props);
            dataSource = new HikariDataSource(config);

        } catch (Exception e) {
            System.err.println("数据库连接池初始化失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
    /**
     * 保存或更新用户授权信息
     * @param companyId 公司ID
     * @param userIp 用户IP地址
     * @param authCode 授权码
     * @return 是否操作成功
     */
    public static boolean saveOrUpdateUserAuth(String companyId, String userIp, String authCode) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            // 先检查记录是否存在
            String checkSql = "SELECT COUNT(*) FROM user_auth WHERE company_id = ? AND user_ip = ?";
            pstmt = conn.prepareStatement(checkSql);
            pstmt.setString(1, companyId);
            pstmt.setString(2, userIp);
            rs = pstmt.executeQuery();

            boolean exists = false;
            if (rs.next()) {
                exists = rs.getInt(1) > 0;
            }
            rs.close();
            pstmt.close();

            if (exists) {
                // 如果记录存在，更新
                String updateSql = "UPDATE user_auth SET auth_code = ?, update_time = NOW() WHERE company_id = ? AND user_ip = ?";
                pstmt = conn.prepareStatement(updateSql);
                pstmt.setString(1, authCode);
                pstmt.setString(2, companyId);
                pstmt.setString(3, userIp);
                return pstmt.executeUpdate() > 0;
            } else {
                // 如果记录不存在，插入
                String insertSql = "INSERT INTO user_auth (company_id, user_ip, auth_code) VALUES (?, ?, ?)";
                pstmt = conn.prepareStatement(insertSql);
                pstmt.setString(1, companyId);
                pstmt.setString(2, userIp);
                pstmt.setString(3, authCode);
                return pstmt.executeUpdate() > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            // 关闭资源
            closeResources(conn, pstmt, rs);
        }
    }

    public static Connection getConnection() throws SQLException {
        if (dataSource == null) {
            throw new SQLException("数据库连接池未初始化");
        }
        return dataSource.getConnection();
    }

    // 关闭资源的通用方法
    public static void closeResources(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            System.err.println("关闭资源失败: " + e.getMessage());
        }
    }

    // 关闭连接池
    public static void shutdown() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            System.out.println("数据库连接池已关闭");
        }
    }
    // 保存或更新公司IP信息
    public static boolean saveOrUpdateCompanyIp(String companyId, String ip) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "UPDATE company_auth_info SET last_login_ip = ?, last_login_time = NOW() WHERE company_id = ?";
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, ip);
            pstmt.setString(2, companyId);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rs);
        }
        return false;
    }

    // 根据公司ID验证授信码
    public static boolean verifyAuthCode(String companyId, String encryptedAuthCode) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try  {
            String sql = "SELECT auth_code FROM company_auth_info WHERE company_id = ?";
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String storedAuthCode = rs.getString("auth_code");
                return storedAuthCode.equals(encryptedAuthCode);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rs);
        }
        return false;
    }

    // 根据公司ID验证授信码
    public static boolean verifyAppAuthCode(String companyId, String encryptedAuthCode) {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try  {
            String sql = "SELECT auth_code FROM company_auth_info WHERE company_id = ?";
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String storedAuthCode = rs.getString("auth_code");
                return "333666999".equals(encryptedAuthCode);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rs);
        }
        return false;
    }
    /**
     * 从数据库中验证用户IP和授信码
     */
    public static boolean verifyUserAuthCode(String userIp, String authCode) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            // 假设数据库中有user_auth表，包含ip和auth_code字段
            String sql = "SELECT COUNT(*) FROM user_auth WHERE ip = ? AND auth_code = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userIp);
            pstmt.setString(2, authCode);

            rs = pstmt.executeQuery();
            if (rs.next()) {
                int count = rs.getInt(1);
                return count > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResources(conn, pstmt, rs);
        }

        return false;
    }
}